package com.hyt.it.ogt.kq.service.bm.den;

import cn.hutool.core.collection.CollectionUtil;
import cn.hutool.core.lang.Assert;
import cn.hutool.core.text.StrPool;
import cn.hutool.core.util.StrUtil;
import cn.hutool.http.HttpException;
import cn.hutool.http.HttpResponse;
import cn.hutool.http.HttpUtil;
import com.hyt.it.ogt.kq.common.bm.enums.CertificateTypeEnum;
import com.hyt.it.ogt.kq.common.bm.utils.DateUtil;
import com.hyt.it.ogt.kq.common.utils.KqStringUtils;
import com.hyt.it.ogt.kq.service.bm.feign.file.FileClient;
import groovy.util.logging.Slf4j;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFPalette;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.ss.util.CellRangeAddressList;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.apache.poi.xssf.usermodel.XSSFDataValidation;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.cloud.context.config.annotation.RefreshScope;
import org.springframework.stereotype.Component;

import javax.servlet.http.HttpServletResponse;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.OutputStream;
import java.lang.reflect.Method;
import java.net.URLEncoder;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.time.LocalDateTime;
import java.util.Date;
import java.util.List;
import java.util.Map;
import java.util.zip.ZipEntry;
import java.util.zip.ZipOutputStream;

/**
 * 资料来源：https://blog.csdn.net/weixin_42740530/article/details/103176839
 *
 * @author ASUS
 */
@RefreshScope
@Component
@Slf4j
public class ExcelExport {
    @Autowired
    private FileClient fileClient;

    public static ExcelExport excelExport;
    public static final int DEFAULT_TABLE_CELL_WIDTH = 18;
    private static final Logger logger = LoggerFactory.getLogger(ExcelExport.class);
    HttpServletResponse response;
    @Value("${fileload.timeout:8000}")
    private int filetimeout;
    // 文件名
    private String fileName;
    // 文件保存路径
    private String fileDir;
    // sheet名
    private String sheetName;
    // 表头字体
    private String titleFontType = "Arial Unicode MS";
    // 表头背景色
    private String titleBackColor = "C1FBEE";
    // 表头字号
    private short titleFontSize = 12;
    // 添加自动筛选的列 如 A:M
    private String address = "";
    // 正文字体
    private String contentFontType = "Arial Unicode MS";
    // 正文字号
    private short contentFontSize = 12;
    // Float类型数据小数位
    private String floatDecimal = "0.00";
    // Double类型数据小数位
    private String doubleDecimal = "0.00";
    // 设置列的公式
    private String[] colFormula = null;

    DecimalFormat floatDecimalFormat = new DecimalFormat(floatDecimal);
    DecimalFormat doubleDecimalFormat = new DecimalFormat(doubleDecimal);

    private HSSFWorkbook workbook = null;

    public ExcelExport() {
        workbook = new HSSFWorkbook();
    }

    public ExcelExport(String fileDir, String sheetName) {
        this.fileDir = fileDir;
        this.sheetName = sheetName;
        workbook = new HSSFWorkbook();
    }

    public ExcelExport(HttpServletResponse response, String fileName, String sheetName) {
        this.fileName = fileName;
        this.response = response;
        this.sheetName = sheetName;
        workbook = new HSSFWorkbook();
    }

    /**
     * 设置表头字体.
     *
     * @param titleFontType
     */
    public void setTitleFontType(String titleFontType) {
        this.titleFontType = titleFontType;
    }

    /**
     * 设置表头背景色.
     *
     * @param titleBackColor 十六进制
     */
    public void setTitleBackColor(String titleBackColor) {
        this.titleBackColor = titleBackColor;
    }

    /**
     * 设置表头字体大小.
     *
     * @param titleFontSize
     */
    public void setTitleFontSize(short titleFontSize) {
        this.titleFontSize = titleFontSize;
    }

    /**
     * 设置表头自动筛选栏位,如A:AC.
     *
     * @param address
     */
    public void setAddress(String address) {
        this.address = address;
    }

    /**
     * 设置正文字体.
     *
     * @param contentFontType
     */
    public void setContentFontType(String contentFontType) {
        this.contentFontType = contentFontType;
    }

    /**
     * 设置正文字号.
     *
     * @param contentFontSize
     */
    public void setContentFontSize(short contentFontSize) {
        this.contentFontSize = contentFontSize;
    }

    /**
     * 设置float类型数据小数位 默认.00
     *
     * @param doubleDecimal 如 ".00"
     */
    public void setDoubleDecimal(String doubleDecimal) {
        this.doubleDecimal = doubleDecimal;
    }

    /**
     * 设置doubel类型数据小数位 默认.00
     *
     * @param floatDecimalFormat 如 ".00
     */
    public void setFloatDecimalFormat(DecimalFormat floatDecimalFormat) {
        this.floatDecimalFormat = floatDecimalFormat;
    }

    /**
     * 设置列的公式
     *
     * @param colFormula 存储i-1列的公式 涉及到的行号使用@替换 如A@+B@
     */
    public void setColFormula(String[] colFormula) {
        this.colFormula = colFormula;
    }

    /**
     * 写excel. xls方式
     *
     * @param titleColumn 对应bean的属性名
     * @param titleName   excel要导出的列名
     * @param titleSize   列宽
     * @param dataList    数据
     */
    public void writeExcel(String[] titleColumn, String[] titleName, int[] titleSize, List<?> dataList) {
        // 添加Worksheet（不添加sheet时生成的xls文件打开时会报错)
        Sheet sheet = workbook.createSheet(this.sheetName);
        // 新建文件
        OutputStream out = null;
        try {
            if (fileDir != null) {
                // 有文件路径
                out = new FileOutputStream(fileDir);
            } else {
                // 否则，直接写到输出流中
                out = response.getOutputStream();
                fileName = fileName + ".xls";
                response.setContentType("application/msexcel;charset=UTF-8");
                response.setHeader("Content-Disposition", "attachment; filename=" + URLEncoder.encode(fileName, "UTF-8"));
            }

            // 写入excel的表头
            Row titleNameRow = workbook.getSheet(sheetName).createRow(0);
            // 设置样式
            CellStyle titleStyle = workbook.createCellStyle();
            titleStyle = (HSSFCellStyle) setFontAndBorder(titleStyle, titleFontType, (short) titleFontSize);
            titleStyle = (HSSFCellStyle) setColor(titleStyle, titleBackColor, (short) 10);

            for (int i = 0; i < titleName.length; i++) {
                sheet.setColumnWidth(i, titleSize[i] * 256); // 设置宽度
                Cell cell = titleNameRow.createCell(i);
                cell.setCellStyle(titleStyle);
                cell.setCellValue(titleName[i].toString());
            }

            // 为表头添加自动筛选
            if (!"".equals(address)) {
                CellRangeAddress c = (CellRangeAddress) CellRangeAddress.valueOf(address);
                sheet.setAutoFilter(c);
            }

            // 通过反射获取数据并写入到excel中
            if (dataList != null && dataList.size() > 0) {
                // 设置样式
                HSSFCellStyle dataStyle = workbook.createCellStyle();
                titleStyle = (HSSFCellStyle) setFontAndBorder(titleStyle, contentFontType, (short) contentFontSize);
                if (titleColumn.length > 0) {
                    for (int rowIndex = 1; rowIndex <= dataList.size(); rowIndex++) {
                        Object obj = dataList.get(rowIndex - 1); // 获得该对象
                        Class clsss = obj.getClass(); // 获得该对对象的class实例
                        Row dataRow = workbook.getSheet(sheetName).createRow(rowIndex);
                        for (int columnIndex = 0; columnIndex < titleColumn.length; columnIndex++) {
                            String title = titleColumn[columnIndex].toString().trim();
                            if (!"".equals(title)) { // 字段不为空
                                if ("studentInfo".equals(title)) {
                                    String UTitle = Character.toUpperCase(title.charAt(0))
                                            + title.substring(1, title.length());
                                    // 使其首字母大写;
                                    String methodName = "get" + UTitle;
                                    // 设置要执行的方法
                                    Method method = clsss.getDeclaredMethod(methodName);
                                    // 获取返回类型
                                    String returnType = method.getReturnType().getName();
                                    Object object = method.invoke(obj);
                                    String data = method.invoke(obj) == null ? "" : object.toString();

                                    Cell cell = dataRow.createCell(columnIndex);
                                    if (data != null && !"".equals(data)) {
                                        if ("int".equals(returnType)) {
                                            cell.setCellValue(Integer.parseInt(data));
                                        } else if ("long".equals(returnType)) {
                                            cell.setCellValue(Long.parseLong(data));
                                        } else if ("float".equals(returnType)) {
                                            cell.setCellValue(floatDecimalFormat.format(Float.parseFloat(data)));
                                        } else if ("double".equals(returnType)) {
                                            cell.setCellValue(doubleDecimalFormat.format(Double.parseDouble(data)));
                                        } else if (Date.class.getName().equals(returnType)) {
                                            cell.setCellValue(
                                                    new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").format(object));
                                        } else {
                                            cell.setCellValue(data);
                                        }
                                    }
                                }
                            } else { // 字段为空 检查该列是否是公式
                                if (colFormula != null) {
                                    String sixBuf = colFormula[columnIndex].replace("@", (rowIndex + 1) + "");
                                    Cell cell = dataRow.createCell(columnIndex);
                                    cell.setCellFormula(sixBuf);
                                }
                            }
                        }
                    }
                }
            }
            workbook.write(out);
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            if (out != null) {
                try {
                    out.close();
                } catch (IOException e) {
                    logger.debug("[Excel文件导出]导出写Excel异常");
                }
            }
        }
    }

    /**
     * xlsx方式
     *
     * @param response
     * @param fileName
     * @param titleColumn
     * @param titleName
     * @param titleSize
     * @param dataList
     */
    public void writeBigExcel(HttpServletResponse response, String fileName, String[] titleColumn, String[] titleName,
                              int[] titleSize, List<?> dataList) {
        try {
            if (titleSize == null) {
                titleSize = new int[titleColumn.length];
                for (int idx = 0; idx < titleSize.length; idx++) {
                    titleSize[idx] = DEFAULT_TABLE_CELL_WIDTH;
                }
            }
            OutputStream out = response.getOutputStream();
            String lastFileName = fileName + ".xlsx";
            response.setContentType("application/msexcel;charset=UTF-8");
            response.setHeader("Content-Disposition", "attachment; filename=" + URLEncoder.encode(lastFileName, "UTF-8"));
            int k = 0;
            int rowIndex;
            Sheet sheet = workbook.createSheet(fileName + (k + 1));
            // 写入excel的表头
            Row titleNameRow = sheet.createRow(0);
            for (int i = 0; i < titleName.length; i++) {
                sheet.setColumnWidth(i, titleSize[i] * 256); // 设置宽度
                Cell cell = titleNameRow.createCell(i);
                cell.setCellValue(titleName[i]);
            }
            // 写入到excel中
            if (dataList != null && dataList.size() > 0) {
                if (titleColumn.length > 0) {
                    for (int index = 0; index < dataList.size(); index++) {
                        // 每个sheet3W条数据
                        if (index != 0 && (index) % 30000 == 0) {
                            k = k + 1;
                            sheet = workbook.createSheet(fileName + (k + 1));
                            // 写入excel的表头
                            titleNameRow = workbook.getSheet(fileName + (k + 1)).createRow(0);
                            for (int i = 0; i < titleName.length; i++) {
                                sheet.setColumnWidth(i, titleSize[i] * 256); // 设置宽度
                                Cell cell = titleNameRow.createCell(i);
                                cell.setCellValue(titleName[i]);
                            }
                        }
                        if (index < 30000) {
                            rowIndex = index + 1;
                        } else {
                            rowIndex = index - 30000 * ((index) / 30000) + 1;
                        }
                        Object obj = dataList.get(index);
                        Class clazz = obj.getClass();
                        Row dataRow = sheet.createRow(rowIndex);
                        for (int columnIndex = 0; columnIndex < titleColumn.length; columnIndex++) {
                            String title = titleColumn[columnIndex].trim();
                            if (!"".equals(title)) {
                                // 获取返回类型
                                String UTitle = Character.toUpperCase(title.charAt(0))
                                        + title.substring(1, title.length());
                                // 使其首字母大写;
                                String methodName = "get" + UTitle;
                                Method method = clazz.getDeclaredMethod(methodName);
                                String returnType = method.getReturnType().getName();
                                Object object = method.invoke(obj);
                                String data = method.invoke(obj) == null ? "" : object.toString();
                                Cell cell = dataRow.createCell(columnIndex);
                                if (data != null && !"".equals(data)) {
                                    if ("int".equals(returnType)) {
                                        cell.setCellValue(Integer.parseInt(data));
                                    } else if ("long".equals(returnType)) {
                                        cell.setCellValue(Long.parseLong(data));
                                    } else if ("float".equals(returnType)) {
                                        cell.setCellValue(new DecimalFormat("0.00").format(Float.parseFloat(data)));
                                    } else if ("double".equals(returnType)) {
                                        cell.setCellValue(new DecimalFormat("0.00").format(Double.parseDouble(data)));
                                    } else if (Date.class.getName().equals(returnType)) {
                                        cell.setCellValue(new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").format(object));
                                    } else if (LocalDateTime.class.getName().equals(returnType)) {
                                        cell.setCellValue(new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").format(object));
                                    } else {
                                        cell.setCellValue(data);
                                    }
                                } else { // 字段为空 检查该列是否是公式
                                    if (colFormula != null) {
                                        String sixBuf = colFormula[columnIndex].replace("@", (rowIndex + 1) + "");
                                        cell = dataRow.createCell(columnIndex);
                                        cell.setCellFormula(sixBuf);
                                    }
                                }
                            }
                        }
                    }
                }
            }
            workbook.write(out);
            out.flush();
            out.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    /**
     * 将16进制的颜色代码写入样式中来设置颜色
     *
     * @param style 保证style统一
     * @param color 颜色：66FFDD
     * @param index 索引 8-64 使用时不可重复
     * @return CellStyle
     */
    public CellStyle setColor(CellStyle style, String color, short index) {
        if ("".equals(color)) {
            // 转为RGB码
            int r = Integer.parseInt((color.substring(0, 2)), 16); // 转为16进制
            int g = Integer.parseInt((color.substring(2, 4)), 16);
            int b = Integer.parseInt((color.substring(4, 6)), 16);
            // 自定义cell颜色
            HSSFPalette palette = workbook.getCustomPalette();
            palette.setColorAtIndex((short) index, (byte) r, (byte) g, (byte) b);

            style.setFillPattern(FillPatternType.SOLID_FOREGROUND);
            style.setFillForegroundColor(index);
        }
        return style;
    }

    /**
     * 设置字体并加外边框
     *
     * @param style    样式
     * @param fontName 字体名
     * @param size     大小
     * @return CellStyle
     */
    public CellStyle setFontAndBorder(CellStyle style, String fontName, short size) {
        HSSFFont font = workbook.createFont();
        font.setFontHeightInPoints(size);
        font.setFontName(fontName);
        font.setBold(true); // 是否加粗
        style.setFont(font);
        style.setBorderBottom(BorderStyle.THIN); // 下边框
        style.setBorderLeft(BorderStyle.THIN); // 左边框
        style.setBorderTop(BorderStyle.THIN); // 上边框
        style.setBorderRight(BorderStyle.THIN); // 右边框
        style.setFillForegroundColor(HSSFColor.RED.index); // 设置背景色
        return style;
    }

    public void creatDropDownList(Sheet taskInfoSheet, DataValidationHelper helper, String[] list,
                                  Integer firstRow, Integer lastRow, Integer firstCol, Integer lastCol) {
        CellRangeAddressList addressList = new CellRangeAddressList(firstRow, lastRow, firstCol, lastCol);
        //设置下拉框数据
        DataValidationConstraint constraint = helper.createExplicitListConstraint(list);
        DataValidation dataValidation = helper.createValidation(constraint, addressList);
        //处理Excel兼容性问题
        if (dataValidation instanceof XSSFDataValidation) {
            dataValidation.setSuppressDropDownArrow(true);
            dataValidation.setShowErrorBox(true);
        } else {
            dataValidation.setSuppressDropDownArrow(false);
        }
        taskInfoSheet.addValidationData(dataValidation);
    }


    /**
     * xlsx方式
     *
     * @param response
     * @param fileName
     * @param titleColumn
     * @param titleName
     * @param titleSize
     * @param dataList
     */
    public void zipMapExcel(HttpServletResponse response, String fileName, String[] titleColumn, String[] titleName, String[] typeName, int[] titleSize, List<Map<String, Object>> dataList)
            throws Exception {
        SXSSFWorkbook sXSSFWorkbook = null;
        try (ZipOutputStream zipOutputStream = new ZipOutputStream(response.getOutputStream())) {
            sXSSFWorkbook = new SXSSFWorkbook(100);
            if (titleSize == null) {
                titleSize = new int[titleColumn.length];
                for (int idx = 0; idx < titleSize.length; idx++) {
                    titleSize[idx] = DEFAULT_TABLE_CELL_WIDTH;
                }
            }
            String lastFileName = fileName + ".zip";
            response.setContentType("application/msexcel;charset=UTF-8");
            response.setHeader("Content-Disposition", "attachment; filename=" + URLEncoder.encode(lastFileName, "UTF-8"));
            int k = 0;
            int rowIndex;
            Sheet sheet = sXSSFWorkbook.createSheet(fileName + (k + 1));
            // 写入excel的表头
            Row titleNameRow = sheet.createRow(0);
            for (int i = 0; i < titleName.length; i++) {
                sheet.setColumnWidth(i, titleSize[i] * 256); // 设置宽度
                Cell cell = titleNameRow.createCell(i);
                cell.setCellValue(titleName[i]);
            }
            // 写入到excel中
            if (CollectionUtil.isNotEmpty(dataList)) {
                if (titleColumn.length > 0) {
                    for (int index = 0; index < dataList.size(); index++) {
                        // 每个sheet3W条数据
                        if (index != 0 && (index) % 30000 == 0) {
                            k = k + 1;
                            sheet = sXSSFWorkbook.createSheet(fileName + (k + 1));
                            // 写入excel的表头
                            titleNameRow = sXSSFWorkbook.getSheet(fileName + (k + 1)).createRow(0);
                            for (int i = 0; i < titleName.length; i++) {
                                sheet.setColumnWidth(i, titleSize[i] * 256); // 设置宽度
                                Cell cell = titleNameRow.createCell(i);
                                cell.setCellValue(titleName[i]);
                            }
                        }
                        if (index < 30000) {
                            rowIndex = index + 1;
                        } else {
                            rowIndex = index - 30000 * ((index) / 30000) + 1;
                        }
                        Map<String, Object> studentInfo = dataList.get(index);
                        Row dataRow = sheet.createRow(rowIndex);

                        String idcard = "";
                        for (int columnIndex = 0; columnIndex < titleColumn.length; columnIndex++) {
                            String title = titleColumn[columnIndex].trim();
                            String type = typeName[columnIndex].trim();
                            if (KqStringUtils.isNotBlank(title)) {
                                Cell cell = dataRow.createCell(columnIndex);
                                String data = studentInfo.get(title) == null ? "" : studentInfo.get(title).toString();

                                switch (title) {
                                    case "createDate":
                                        String createDate = DateUtil.getStringFormatTimeStamp(data);
                                        cell.setCellValue(createDate);
                                        break;
                                    case "姓名":
                                        // 进来默认给姓名为idcard，防止没有idcard的情况导出图片名称不对
                                        idcard = data;
                                        cell.setCellValue(data);
                                        break;
                                    case "证件号码":
                                        idcard = data;
                                        cell.setCellValue(idcard);
                                        break;
                                    case "性别":
                                        if (data.equals("0")) {
                                            cell.setCellValue("男");
                                        } else if (data.equals("1")) {
                                            cell.setCellValue("女");
                                        } else {
                                            cell.setCellValue(data);
                                        }
                                        break;
                                    case "证件类型":
                                        String desc = CertificateTypeEnum.getDescByType(data);
                                        cell.setCellValue(desc);
                                        break;
                                    default:
                                        if (StrUtil.isNotBlank(data)) {
                                            if (data.equals("true")) {
                                                cell.setCellValue("是");
                                                break;
                                            } else if (data.equals("false")) {
                                                cell.setCellValue("否");
                                                break;
                                            }

                                            // 将文件类型的列进行文件保存下载
                                            if ("file".equals(type)) {
                                                data = this.getFileTotalPath(data, columnIndex, idcard, zipOutputStream, cell);
                                                cell.setCellValue(data);
                                                break;
                                            } else if ("cloudFile".equals(type)) {
                                                //data = this.getCloudFileTotalPath(data, columnIndex, idcard, zipOutputStream, cell);
                                                cell.setCellValue(data);
                                                break;
                                            }
                                        }

                                        cell.setCellValue(data);
                                        break;
                                }
                            }
                        }
                    }
                }
            }
            ZipEntry entry = new ZipEntry("考生列表" + ".xls");
            zipOutputStream.putNextEntry(entry);
            sXSSFWorkbook.write(zipOutputStream);
            zipOutputStream.flush();
        } catch (Exception e) {
            logger.error("[Excel文件导出] 导出Excel列表异常", e);
        } finally {
            if (null != sXSSFWorkbook) {
                sXSSFWorkbook.close();
            }
        }
    }

    /**
     * 获取cloudFile的文件路径并且分包进zip
     *
     * @param data
     * @param columnIndex
     * @param idcard
     * @param zipOutputStream
     * @param cell
     * @return
     * @throws IOException
     */
    public String getCloudFileTotalPath(String data, Integer columnIndex, String idcard, ZipOutputStream zipOutputStream, Cell cell) throws IOException {
        String[] pathStr = data.split(StrPool.COMMA);
        if (pathStr.length > 0) {
            String totalPath = "";
            int num = 0;
            for (int j = 0; j < pathStr.length; j++) {
                if (KqStringUtils.isNotBlank(pathStr[j])) {
                    num += 1;
                    // 这里将云服务商的图片获取
                    byte[] fileBytes = downloadBytes(pathStr[j]);

                    if (fileBytes != null && fileBytes.length > 0) {
                        String path = "image" + columnIndex + "/";
                        String extendsName = pathStr[j].substring(pathStr[j].lastIndexOf("."));
                        if (num > 1) {
                            data = path + idcard + "-" + num + extendsName;
                        } else {
                            data = path + idcard + extendsName;
                        }
                        ZipEntry entry = new ZipEntry(data);
                        entry.setSize(fileBytes.length);
                        zipOutputStream.putNextEntry(entry);
                        zipOutputStream.write(fileBytes);
                        totalPath += data + ";";
                    }
                }
            }
            if (KqStringUtils.isNotBlank(totalPath)) {
                totalPath = totalPath.substring(0, totalPath.length() - 1);
                cell.setCellFormula("HYPERLINK(\"" + totalPath.split(";")[0] + "\",\"" + totalPath.split(";")[0] + "\")");
            }
            data = totalPath;
        }
        return data;
    }

    private static HttpResponse requestDownload(String url, int timeout) {
        Assert.notBlank(url, "[url] is blank !");

        final HttpResponse response = HttpUtil.createGet(url, true)
                .timeout(timeout)
                .executeAsync();

        if (response.isOk()) {
            return response;
        }

        throw new HttpException("Server response error with status code: [{}]", response.getStatus());
    }

    private byte[] downloadBytes(String url) {
        try {
            return requestDownload(url, filetimeout).bodyBytes();
        } catch (Exception e) {
            logger.error("[Excel文件导出] 导出excel下载图片异常url：{}", url);
            throw e;
        }
    }

    /**
     * 获取为file类型的文件路径并且分包进zip
     *
     * @param data
     * @param columnIndex
     * @param idcard
     * @param zipOutputStream
     * @param cell
     * @return
     * @throws IOException
     */
    public String getFileTotalPath(String data, Integer columnIndex, String idcard, ZipOutputStream zipOutputStream, Cell cell) throws IOException {
        String[] pathStr = data.split(StrPool.COMMA);
        if (pathStr.length > 0) {
            String totalPath = "";
            int num = 0;
            for (int j = 0; j < pathStr.length; j++) {
                if (KqStringUtils.isNotBlank(pathStr[j])) {
                    num += 1;
                    byte[] fileBytes = fileClient.downFile(pathStr[j]);
                    if (fileBytes != null && fileBytes.length > 0) {
                        String path = "image" + columnIndex + "/";
                        String extendsName = pathStr[j].substring(pathStr[j].lastIndexOf("."));
                        if (num > 1) {
                            data = path + idcard + "-" + num + extendsName;
                        } else {
                            data = path + idcard + extendsName;
                        }
                        ZipEntry entry = new ZipEntry(data);
                        entry.setSize(fileBytes.length);
                        zipOutputStream.putNextEntry(entry);
                        zipOutputStream.write(fileBytes);
                        totalPath += data + ";";
                    }
                }
            }
            if (KqStringUtils.isNotBlank(totalPath)) {
                totalPath = totalPath.substring(0, totalPath.length() - 1);
                cell.setCellFormula("HYPERLINK(\"" + totalPath.split(";")[0] + "\",\"" + totalPath.split(";")[0] + "\")");
            }
            data = totalPath;
        }
        return data;
    }

    /**
     * 导出excel并压缩文件
     *
     * @param response
     * @param fileName
     * @param titleColumn
     * @param titleName
     * @param titleSize
     * @param dataList
     */
    public void exportMapExcel(HttpServletResponse response, String fileName, String[] titleColumn, String[] titleName,
                               int[] titleSize, List<Map<String, Object>> dataList) {
        try {
            if (titleSize == null) {
                titleSize = new int[titleColumn.length];
                for (int idx = 0; idx < titleSize.length; idx++) {
                    titleSize[idx] = DEFAULT_TABLE_CELL_WIDTH;
                }
            }
            OutputStream out = response.getOutputStream();
            String lastFileName = fileName + ".xls";
            response.setContentType("application/vnd.ms-excel");
            response.setCharacterEncoding("UTF-8");
            response.setHeader("Content-Disposition", "attachment; filename=" + URLEncoder.encode(lastFileName, "UTF-8"));
            int k = 0;
            int rowIndex;
            Sheet sheet = workbook.createSheet(fileName + (k + 1));
            // 写入excel的表头
            Row titleNameRow = workbook.getSheet(fileName + (k + 1)).createRow(0);
            for (int i = 0; i < titleName.length; i++) {
                sheet.setColumnWidth(i, titleSize[i] * 256); // 设置宽度
                Cell cell = titleNameRow.createCell(i);
                cell.setCellValue(titleName[i]);
            }
            // 写入到excel中
            if (dataList != null && dataList.size() > 0) {
                if (titleColumn.length > 0) {
                    for (int index = 0; index < dataList.size(); index++) {
                        // 每个sheet3W条数据
                        if (index != 0 && (index) % 30000 == 0) {
                            k = k + 1;
                            sheet = workbook.createSheet(fileName + (k + 1));
                            // 写入excel的表头
                            titleNameRow = workbook.getSheet(fileName + (k + 1)).createRow(0);
                            for (int i = 0; i < titleName.length; i++) {
                                sheet.setColumnWidth(i, titleSize[i] * 256); // 设置宽度
                                Cell cell = titleNameRow.createCell(i);
                                cell.setCellValue(titleName[i]);
                            }
                        }
                        if (index < 30000) {
                            rowIndex = index + 1;
                        } else {
                            rowIndex = index - 30000 * ((index) / 30000) + 1;
                        }
                        Map<String, Object> obj = dataList.get(index);
                        Class clazz = obj.getClass();
                        Row dataRow = workbook.getSheet(fileName + (k + 1)).createRow(rowIndex);
                        for (int columnIndex = 0; columnIndex < titleColumn.length; columnIndex++) {
                            String title = titleColumn[columnIndex].trim();
                            if (!"".equals(title)) {
                                System.out.println(title);
                                String data = obj.get(title) == null ? "" : obj.get(title).toString();
                                Cell cell = dataRow.createCell(columnIndex);
                                if (data != null && !"".equals(data)) {
                                    if ("性别".equals(title)) {
                                        if (data.equals("0")) {
                                            cell.setCellValue("男");
                                        } else if (data.equals("1")) {
                                            cell.setCellValue("女");
                                        } else {
                                            cell.setCellValue(data);
                                        }
                                    }
                                    if ("create_date".equals(title)) {
                                        Date date = (Date) obj.get(title);
                                        cell.setCellValue(new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").format(date));
                                    } else {
                                        if (data.equals("true")) {
                                            cell.setCellValue("是");
                                        } else if (data.equals("false")) {
                                            cell.setCellValue("否");
                                        } else {
                                            cell.setCellValue(data);
                                        }
                                    }

                                } else {
                                    cell.setCellValue("");
                                }
                            }
                        }
                    }
                }
            }
            workbook.write(out);
            out.flush();
            out.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    /**
     * 导出excel并
     *
     * @param response
     * @param fileName
     * @param dataList
     */
    public void exporExcelDemo(HttpServletResponse response, String fileName, List<Object> dataList) {
        try (SXSSFWorkbook workbook = new SXSSFWorkbook(100)) {
            OutputStream out = response.getOutputStream();
            String lastFileName = fileName + ".xlsx";
            response.setContentType("application/msexcel;charset=UTF-8");
            response.setHeader("Content-Disposition", "attachment; filename=" + URLEncoder.encode(lastFileName, "UTF-8"));
            int k = 0;
            Sheet sheet = workbook.createSheet(fileName + (k + 1));
            // 写入excel的表头
            Row titleNameRow = workbook.getSheet(fileName + (k + 1)).createRow(0);
            int[] titleSize = new int[3];
            String[] titleName = new String[3];
            titleSize[0] = DEFAULT_TABLE_CELL_WIDTH;
            titleName[0] = "姓名";
            titleSize[1] = DEFAULT_TABLE_CELL_WIDTH;
            titleName[1] = "证件类型";
            titleSize[2] = DEFAULT_TABLE_CELL_WIDTH;
            titleName[2] = "证件号码";

            for (int i = 0; i < titleName.length; i++) {
                // 设置宽度
                sheet.setColumnWidth(i, titleSize[i] * 256);
                Cell cell = titleNameRow.createCell(i);
                cell.setCellValue(titleName[i]);
                if (i == 2) {
                    CellStyle cellStyle = workbook.createCellStyle();
                    DataFormat format = workbook.createDataFormat();
                    cellStyle.setDataFormat(format.getFormat("@"));
                    cell.setCellStyle(cellStyle);
                    cell.setCellType(CellType.STRING);
                }
            }
            // 写入到excel中
            String optionStr[] = {"大陆居民身份证", "港澳台身份证", "护照"};

            DataValidationHelper helper = sheet.getDataValidationHelper();
            DataValidationConstraint constraint = helper.createExplicitListConstraint(optionStr);
            CellRangeAddressList regions = new CellRangeAddressList(1, 100, 1, 1);
            DataValidation dataValidation = helper.createValidation(constraint, regions);
            sheet.addValidationData(dataValidation);

            workbook.write(out);
            out.flush();
            out.close();
        } catch (Exception e) {
            logger.error("[Excel文件导出] exporExcelDemo error", e);
        }
    }
}
